﻿using Excel2DbTool.Common;
using Excel2DbTool.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;

namespace Excel2DbTool.Helpers
{
    public class DbTableMigraterManager
    {
        IEnumerable<DbTableMigrater> migraters = null;

        public DbTableMigraterManager()
        {
            migraters = new List<DbTableMigrater>()
            {
                new XMTableMigrater(),
                //new ZRZTableMigrater(),
                //new LJZTableMigrater(),
                //new CTableMigrater(),
                //new HTableMigrater()
            };
        }

        public void Execute()
        {
            foreach (var item in migraters)
            {
                item.Execute();
            }

        }

    }


    public abstract class DbTableMigrater
    {
        
        IList<DbRowMapping> dbRowMappings;

        /// <summary>
        /// 原数据库中选取出目标Table中的数据时使用的KEY
        /// </summary>
        readonly string groupKey = string.Empty;
        readonly string targetTableName = string.Empty;

        public DbTableMigrater(string groupKey, string targetTableName)
        {
            dbRowMappings = new List<DbRowMapping>();
            this.groupKey = groupKey;
            this.targetTableName = targetTableName;
        }

        protected abstract IList<DbFieldMapping> DbFieldMappings { get; }

        public void Execute()
        {
            DataTable dt = GetDataWhichNeedInsertToTable();

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DbRowMapping dbrm = GetDbRowMapping();

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    var val = dt.Rows[i][j];
                    dbrm[j].Val = val;
                }

                dbRowMappings.Add(dbrm);
            }


            InsertDataToDb();
        }

        DataTable GetDataWhichNeedInsertToTable()
        {
            string sql = String.Format("SELECT {0} FROM {1} where rowid=any(select max(rowid) from {1} group by {2})", GetFieldStringNeedReslove(), ConfigHelper.TableName, groupKey);
            return SqlHelper.GetInstance().Select(sql);
        }

        protected String GetFieldStringNeedReslove()
        {//
            IEnumerable<String> orignFieldNames = DbFieldMappings.Where(dbfm => dbfm.OrignFieldName != OrginTableFieldNames.NONE)
                                                                .Select(dbfm => dbfm.OrignFieldName)
                                                                .ToList();
            return string.Join(",", orignFieldNames);
        }



        //最终的DbRowMapping对字段的映射其实不感兴趣,只是对最终形成的插入SQL字符串中的VALUES中字段出现的顺序感兴趣
        //因此我们将由XMTableMigrater维护的字段顺序复制一份给DbRowMapping
        protected DbRowMapping GetDbRowMapping()
        {
            IList<IDbFieldVal> dbfms = new List<IDbFieldVal>(DbFieldMappings.Count);

            foreach (var item in DbFieldMappings)
            {
                dbfms.Add(item.GetDbFieldVal());
            }

            DbRowMapping dbrm = new DbRowMapping(dbfms);

            return dbrm;
        }

        void InsertDataToDb()
        {
            string insertSql = getInsertSqlString();
            SqlHelper.GetInstance().Insert(insertSql);
        }

        string getInsertSqlString()
        {
            StringBuilder sb = new StringBuilder(20000);
            sb.AppendFormat("INSERT INTO {0} ({1})", targetTableName, GetFieldStringNeedInsert());


            for (int i = 0; i < dbRowMappings.Count(); i++)
            {
                sb.AppendFormat(" SELECT {0} FROM dual {1}", dbRowMappings.ElementAt(i).GetValStr(), (i == dbRowMappings.Count() - 1) ? "" : "union");
            }

            return sb.ToString();
        }

        String GetFieldStringNeedInsert()
        {
            IEnumerable<String> targetFieldNames = DbFieldMappings.Select(dbfm => dbfm.TargetFieldName).ToList();
            return string.Join(",", targetFieldNames);
        }

    }
}
